<?php

//ini_set("display_errors",1);

//require_once 'PHPExcel-1.8/Classes/PHPExcel.php';
namespace app\admin\controller;
use think\Db;
include 'PHPExcel-1.8/Classes/PHPExcel.php';
include 'PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
class Import extends Base
{
    private function minqiang($item){
        if (is_object($item)){
            return $item->getPlainText();
        }else {
            return $item;
        }
    }

    public function import()
    {
        if(request()->isPost()) {
            $a = 0;
            $b = 0;
            $param = input('post.ufile');
            if(empty($param)){
                echo "<script>alert('请添加.xlsx文件');</script>";
                return $this->fetch();
            }
            ini_set("display_errors", 1);
            define('EXCEL_EXTENSION_2003', "xls");
            define('EXCEL_EXTENSION_2007', "xlsx");
//            $fileName2007 = "excel/1.xlsx";
//        halt(file_exists($fileName2007));
            $fileName2007 = "uploads/file/".$param;
            $fileName = $fileName2007;
            $reader = new \PHPExcel_Reader_Excel2007();
            $PHPExcel = $reader->load($fileName);
            $worksheet = $PHPExcel->getActiveSheet();
            $highestRow = $worksheet->getHighestRow(); // 取得总行数
            $highestColumm = $worksheet->getHighestColumn(); // 取得总列数
//            if($highestColumm>4000){
//                echo "<script>alert('超过单次导入上限5000条');</script>";
//            }
            $highestColumm = \PHPExcel_Cell::columnIndexFromString($highestColumm); //字母列转换为数字列 如:AA变为27
            $date=date("Y-m");//获取当前时间
            if(!file_exists('uploads/images/'.$date))
            {
                mkdir('uploads/images/'.$date,0777);         //创建文件夹
            }
            $imageInfo = $this->extractImageFromWorksheet($worksheet, "uploads/images/".$date,$date);

            /** 循环读取每个单元格的数据 */
            $result = array();
            $a=0;
            for ($row = 2; $row <= $highestRow; $row++) {//行数是以第2行开始
                $item = array();
                $item['create_time'] = time();
                if (!isset($imageInfo[$row])){
                    break;
                }
//                print_r( $imageInfo[$row]['img']);
                $item['pic'] = $imageInfo[$row]['img'];
                for ($column = 1; $column < $highestColumm; $column++) {//列数是以第1列开始
                    switch ($column) {
                        case 1: {
                            $item['name'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['name'] = self::minqiang($item['name']);
                            break;
                        }
                        case 2: {
                            $item['type'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['type'] = self::minqiang($item['type']);
                            break;
                        }
                        case 3: {

                            $item['registration'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['registration'] = self::minqiang($item['registration']);
                            break;
                        }
                        case 4: {
                            $item['approved'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['approved'] = self::minqiang($item['approved']);
                            break;
                        }
                        case 5: {
                            $item['cluster'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['cluster'] = self::minqiang($item['cluster']);
                            break;
                        }
                        case 6: {
                            $item['price'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['price'] = self::minqiang($item['price']);
                            break;
                        }
                        case 7: {
                            $item['phone'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['phone'] = self::minqiang($item['phone']);
                            break;
                        }
                        case 8: {
                            $item['shows'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['shows'] = self::minqiang($item['shows']);
                            break;
                        }
                        case 9: {
                            $item['whetherown'] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                            $item['whetherown'] = self::minqiang($item['whetherown']);
                            break;
                        }
                    }
                }
                $registration =$item['registration'];
                $repeat = Db::query('select * from think_auditing where registration = ?',[$registration]);

                if(empty($repeat)) {
                    array_push($result, $item);
                    $b = $b+1;
                }else{
                    $a=$a+1;
                }
            }
//            halt($result);
            foreach ($result as $item) {
                Db::name('auditing')->insert($item);
            }
                $msg = '有'.$a.'条记录注册号重复,有'.$b.'条记录插入成功';
                echo "<script>alert('$msg');</script>";
        }

        return $this->fetch();
    }
    public function getExtendFileName($file_name)
    {

        $extend = pathinfo($file_name);
        $extend = strtolower($extend["extension"]);
        return $extend;
    }

    public function extractImageFromWorksheet($worksheet, $basePath,$date)
    {

//        $result = array();
        $imageFileName = "";
//        halt($worksheet->getDrawingCollection());
        foreach ($worksheet->getDrawingCollection() as $drawing) {
//            halt($drawing);
            $path = $basePath;
            $filename = $drawing->getPath();
            $qq=$drawing->getCoordinates();
//           print_r($map1);
            $suiji = rand(1000, 9999);
            $imageFileName =$date.'/'.time() . '_' . $suiji . $drawing->getIndexedFilename();
            $path = $path .'/'. time() . '_' . $suiji . $drawing->getIndexedFilename();
            copy($filename, $path);
            $ww=preg_replace('/[^\.0123456789]/s', '',$qq);
            $map=array('img'=>$imageFileName,'id'=>$ww);
            $map1[$map['id']] = $map;

        }

        return $map1;
    }
    public function export(){
        $data= Db::query('select * from think_auditing');
        $name='Excelfile';
        $res=service('ExcelToArrary')->push($data,$name);

    }

    public function push($data,$name){
        error_reporting(E_ALL);
        date_default_timezone_set('Europe/London');
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("php爱好者")
            ->setLastModifiedBy("php爱好者")
            ->setTitle("数据EXCEL导出")
            ->setSubject("数据EXCEL导出")
            ->setDescription("备份数据")
            ->setKeywords("excel")
            ->setCategory("result file");
        foreach($data as $k => $v){
            $num=$k+1;
            $objPHPExcel->setActiveSheetIndex(0)
//Excel的第A列，uid是你查出数组的键值，下面以此类推
                ->setCellValue('A'.$num, $v['uid'])
                ->setCellValue('B'.$num, $v['email'])
                ->setCellValue('C'.$num, $v['password']);
}
        $objPHPExcel->getActiveSheet()->setTitle('User');
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$name.'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }


}